#!/usr/bin/env python
# coding: utf-8
import web
from config import settings
from datetime import datetime
from config.settings import session
from StringIO import StringIO
import csv
import authbase

render = settings.render
db = settings.db

class Index(authbase.AuthBase):

    def GET(self,page=1):
        page = int(page)
        perpage = settings.perpage_count
        offset = (page - 1) * perpage
        i = web.input()
        print i
        station_name = i.get('station_name', None)
        where_str=" 1=1 "
        if station_name:
            where_str=where_str+" and site='"+station_name+"'"
            selected_station_name=station_name
        else:
            selected_station_name=None

        start_date = i.get('dateStart', None)
        if start_date:
            where_str=where_str+" and collection_time >=timestamp '"+start_date+"'"
            selected_start_date=start_date
        else:
            selected_start_date=None

        end_date = i.get('dateEnd', None)
        if end_date:
            where_str=where_str+" and collection_time <=timestamp '"+end_date+"'"
            selected_end_date=end_date
        else:
            selected_end_date=None

        data_list =db.query("SELECT * FROM site_data  where "+where_str+" limit "+str(perpage)+" offset "+str(offset))
        count = db.query("SELECT COUNT(*) AS count FROM site_data where "+where_str)[0]
        pages = count.count / perpage
        if count.count % perpage > 0:
            pages += 1
        stations=list(db.select('stations'))
        return render.raw_data.index(data_list=data_list, pages=pages,current_page=page,stations=stations,count=count,selected_station_name=selected_station_name,selected_start_date=selected_start_date,selected_end_date=selected_end_date)

class Export(authbase.AuthBase):
    def GET(self):
        i = web.input()
        station_name = i.get('station_name', None)
        where_str=" 1=1 "
        if station_name:
            where_str=where_str+" and site='"+station_name+"'"
            selected_station_name=station_name
        else:
            selected_station_name=None
        start_date = i.get('dateStart', None)
        if start_date:
            where_str=where_str+" and collection_time >=timestamp '"+start_date+"'"
            selected_start_date=start_date
        else:
            selected_start_date=None
        end_date = i.get('dateEnd', None)
        if end_date:
            where_str=where_str+" and collection_time <=timestamp '"+end_date+"'"
            selected_end_date=end_date
        else:
            selected_end_date=None
        data_list =db.query("SELECT * FROM site_data  where "+where_str+" order by collection_time ")
        file_name = station_name+u'数据.csv'
        csv_file = StringIO()
        csv_writer = csv.writer(csv_file)
        csv_writer.writerow(['NO₂(μg/m³)', 'O₃(μg/m³)','PM10(μg/m³)','PM2.5(μg/m³)','CO(mg/m³)','SO₂(μg/m³)','湿度(%rh)',
                             '风向(a)','风速(m/s)','大气压(MPa)','温度(°C)','监测时间'])
        for data in data_list:
            csv_writer.writerow([data.no2, data.o3, data.pm10, data.pm25, data.co, data.so2, data.humidity,\
                                 data.wind_direction, data.wind_speed, data.atmosphere, data.temperature, data.collection_time])

        web.header("Content-Disposition", "attachment; filename=%s" % file_name)
        web.header("Content-Type", "text/csv")

        return csv_file.getvalue()
